package blob;

import org.junit.Test;
import util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/*
* 使用preparedStatement来实现更加高效的数据批量操作
*
* update、delete 本身就具有批量操作的效果
* 此时的批量操作，主要指的是批量插入。使用PreparedStatement如何实现更加高效的批量插入
*
* 题目：向goods表中插入20000条数据
* 方式一：使用statement
*   Connection conn = JDBCUtil.getConnection();
*   Statement st = conn.createStatement();
*   for(int i = 1; i <= 20000; i++){
*       String sql = "insert into goods(name) values('name_"+i+"')";
*       st.execute(sql);
*   }
* */
public class InsertTest {

    /*
     * 批量插入的方式二：使用PreparedStatement
     */
    @Test
    public void testInsert1() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //当前开始的时间
            long startTime = System.currentTimeMillis();
            //程序执行体
            connection = JDBCUtils.getConnection();
            String sql = "insert into goods(name) values(?)";
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i < 20000; i++) {
                preparedStatement.setString(1, "name_"+i);
                preparedStatement.execute();
            }
            //程序执行后的时间
            long endTime = System.currentTimeMillis();
            System.out.println("程序执行花费的时间为：" + (endTime - startTime));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResourceConnection(connection, preparedStatement);
        }
    }


    /*
     * 批量插入的方式三：
     * 1.addBatch()、executeBatch()、clearBatch()
     * 2.mysql服务默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持
     *     ?rewriteBatchedStatements=true 写在配置文件的url后面
     * 3.使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
     */
    @Test
    public void testInsert2() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //当前开始的时间
            long startTime = System.currentTimeMillis();
            //程序执行体
            connection = JDBCUtils.getConnection();
            String sql = "insert into goods(name) values(?)";
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i < 20000; i++) {
                preparedStatement.setString(1, "name_"+i);

                //1.攒"sql"
                preparedStatement.addBatch();

                if (i % 500 == 0) {
                    //2.执行batch
                    preparedStatement.execute();
                    //3.清空batch
                    preparedStatement.clearBatch();
                }

            }
            //程序执行后的时间
            long endTime = System.currentTimeMillis();
            System.out.println("程序执行花费的时间为：" + (endTime - startTime));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResourceConnection(connection, preparedStatement);
        }
    }


    /*
     * 批量插入的方式四：
     * 1.设置连接不允许自动提交数据
     *     connection.setAutoCommit(false);
     * 2.手动提交数据
     *     connection.commit();
     */
    @Test
    public void testInsert3() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //当前开始的时间
            long startTime = System.currentTimeMillis();
            //程序执行体
            connection = JDBCUtils.getConnection();

            //不允许自动提交数据
            connection.setAutoCommit(false);

            String sql = "insert into goods(name) values(?)";
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i < 20000; i++) {
                preparedStatement.setString(1, "name_"+i);

                //1.攒"sql"
                preparedStatement.addBatch();

                if (i % 500 == 0) {
                    //2.执行batch
                    preparedStatement.execute();
                    //3.清空batch
                    preparedStatement.clearBatch();
                }
            }

            //手动提交数据
            connection.commit();

            //程序执行后的时间
            long endTime = System.currentTimeMillis();
            System.out.println("程序执行花费的时间为：" + (endTime - startTime));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResourceConnection(connection, preparedStatement);
        }
    }
}
